﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;

namespace Trying1.Classes
{
    class BookingDBManager
    {
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Booking";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    m.totalPrice = (decimal)dr["totalPrice"];
                    

                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static ArrayList GetRoomByDate(DateTime cid, DateTime cod)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select b.roomNum from Booking b inner join Room r on b.roomNum = r.roomNum where b.checkInDate <=@checkOutDate and b.checkOutDate > @checkInDate;";
                comm.Parameters.AddWithValue("@checkInDate", cid);
                comm.Parameters.AddWithValue("@checkOutDate", cod);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking m = new Booking();
                    m.roomNum = (int)dr["roomNum"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static Booking GetBookingByCheckInDate(DateTime checkInDate)
        {
            Booking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE checkInDate=@checkInDate";
                comm.Parameters.AddWithValue("@checkInDate", checkInDate);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    m.totalPrice = (decimal)dr["totalPrice"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static Booking GetBookingByroomNum(int roomNum)
        {
            Booking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    m.totalPrice = (decimal)dr["totalPrice"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }


        public static int InsertBooking(Booking m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Booking(roomNum,guestNo,checkInDate,checkOutDate,addReq,roomRates,totalPrice)" +
                    " VALUES (@roomNum,@guestNo,@checkInDate,@checkOutDate,@addReq,@roomRates,@totalPrice) WHERE Booking.roomRates = Room.roomRate";
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                comm.Parameters.AddWithValue("@guestNo", m.guestNo);
                comm.Parameters.AddWithValue("@checkInDate", m.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", m.checkOutDate);
                comm.Parameters.AddWithValue("@addReq", m.addReq);
                comm.Parameters.AddWithValue("@roomRates", m.roomRates);
                comm.Parameters.AddWithValue("@totalPrice", m.totalPrice);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int InsertBookingbyGuest(Booking m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Booking(roomNum,guestNo,checkInDate,checkOutDate,addReq,roomRates)" +
                     "VALUES (@roomNum,@guestNo,@checkInDate,@checkOutDate,@addReq,(Select roomRate from Room where roomNum=@roomNum)); ";
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                comm.Parameters.AddWithValue("@guestNo", m.guestNo);
                comm.Parameters.AddWithValue("@checkInDate", m.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", m.checkOutDate);
                comm.Parameters.AddWithValue("@addReq", m.addReq);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateBooking(Booking m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET roomNum=@roomNum,guestNo=@guestNo,checkInDate=@checkInDate,checkOutDate=@checkOutDate,addReq=@addReq,totalPrice=@totalPrice where bookingId=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", m.bookingId);
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                comm.Parameters.AddWithValue("@guestNo", m.guestNo);
                comm.Parameters.AddWithValue("@checkInDate", m.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", m.checkOutDate);
                comm.Parameters.AddWithValue("@addReq", m.addReq);
                comm.Parameters.AddWithValue("@roomRates", m.roomRates);
                comm.Parameters.AddWithValue("@totalPrice", m.totalPrice);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteBooking(int bookingId)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from Booking where bookingId=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", bookingId);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        public static int DeleteGuestBooking(int guestNo)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from Booking where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static ArrayList GetAllGuestNo()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Booking";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
    }
}
